using System;
using System.IO;
using System.Data;
using Publish.Services;
using System.Data.Common;
using System.Collections.Generic;

namespace Publish.Utility
{
    public class SqlHelper
    {
        private readonly String connectionString = String.Empty;
        private const String ProviderName = "System.Data.SqlClient";

        #region

        public SqlHelper()
        {
            #region 读取 Xml 里面的数据库连接字符串信息

            if (!String.IsNullOrEmpty(connectionString) || !File.Exists(XmlService.FileName)) return;//配置文件不存在或已经赋值则返回

            var service = new XmlService();
            foreach (AppSettings settings in service.GetAllAppSettings())
            {
                if (!String.IsNullOrEmpty(settings.Id))
                {
                    connectionString = settings.Value;//连接字符串赋值
                }
            }
            
            #endregion
        }

        #endregion

        #region Public Method

        #region Procedure Query

        /// <summary>
        /// 执行无参的存储过程
        /// </summary>
        /// <param name="sqlCommand">存储过程名</param>
        /// <returns></returns>
        public int ExecuteNonQueryProc(String sqlCommand)
        {
            int result = 0;
            using (DbConnection connection = GetConnection())
            {
                DbCommand command = GetCommand(sqlCommand, CommandType.StoredProcedure, connection);
                connection.Open();
                result = command.ExecuteNonQuery();
                command.Parameters.Clear();
            }
            return result;
        }

        /// <summary>
        /// 执行无返回值有参数的存储过程
        /// </summary>
        /// <param name="sqlCommand">存储过程名</param>
        /// <param name="parameters">参数</param>
        /// <returns></returns>
        public int ExecuteNonQueryProc(String sqlCommand, Dictionary<String, object> parameters)
        {
            int result = 0;
            using (DbConnection connection = GetConnection())
            {
                DbCommand command = GetCommand(sqlCommand, CommandType.StoredProcedure, connection);
                foreach (KeyValuePair<String, object> p in parameters)
                {
                    command.Parameters.Add(GetParameter(p.Key, p.Value, command));
                }
                connection.Open();
                result = command.ExecuteNonQuery();
                command.Parameters.Clear();
            }
            return result;
        }

        /// <summary>
        /// 执行有返回值无参数的存储过程
        /// </summary>
        /// <param name="cmdText"></param>
        /// <returns></returns>
        public object ExecuteScalarProc(String cmdText)
        {
            using (DbConnection connection = GetConnection())
            {
                DbCommand command = GetCommand(cmdText, CommandType.StoredProcedure, connection);
                connection.Open();
                object val = command.ExecuteScalar();
                command.Parameters.Clear();
                return val;
            }
        }

        /// <summary>
        /// 执行有返回值的有参数的存储过程
        /// </summary>
        /// <param name="cmdText">存储过程名</param>
        /// <param name="para">参数</param>
        /// <returns></returns>
        public object ExecuteScalarProc(String cmdText, Dictionary<String, object> para)
        {
            using (DbConnection connection = GetConnection())
            {
                DbCommand command = GetCommand(cmdText, CommandType.StoredProcedure, connection);
                foreach (KeyValuePair<String, object> p in para)
                {
                    command.Parameters.Add(GetParameter(p.Key, p.Value, command));
                }
                connection.Open();
                object val = command.ExecuteScalar();
                command.Parameters.Clear();
                return val;
            }
        }

        /// <summary>
        /// 执行无参数的存储过程,返回DbDataReader对象
        /// </summary>
        /// <param name="sqlCommand">存储过程名</param>
        /// <returns></returns>
        public DbDataReader GetReaderProc(String sqlCommand)
        {
            try
            {
                DbConnection connection = GetConnection();
                DbCommand command = GetCommand(sqlCommand, CommandType.StoredProcedure, connection);
                connection.Open();
                DbDataReader reader = command.ExecuteReader(CommandBehavior.CloseConnection);
                return reader;
            }
            catch (Exception ex)
            {
                Console.Write("" + ex.Message);
                return null;
            }
        }

        /// <summary>
        /// 执行有参数的存储过程,返回DbDataReader对象
        /// </summary>
        /// <param name="sqlCommand"></param>
        /// <param name="parameters"></param>
        /// <returns></returns>
        public DbDataReader GetReaderProc(String sqlCommand, Dictionary<String, object> parameters)
        {
            try
            {
                DbConnection connection = GetConnection();
                DbCommand command = GetCommand(sqlCommand, CommandType.StoredProcedure, connection);
                foreach (KeyValuePair<String, object> p in parameters)
                {
                    command.Parameters.Add(GetParameter(p.Key, p.Value, command));

                }
                connection.Open();
                DbDataReader reader = command.ExecuteReader(CommandBehavior.CloseConnection);
                command.Parameters.Clear();
                return reader;
            }
            catch
            {
                return null;
            }
        }

        #endregion

        #region Sql Query

        /// <summary>
        /// 执行无返回值的sql语句
        /// </summary>
        /// <param name="sqlCommand"></param>
        public int ExecuteNonQuery(String sqlCommand)
        {
            int result = 0;
            using (DbConnection connection = GetConnection())
            {
                DbCommand command = GetCommand(sqlCommand, CommandType.Text, connection);
                connection.Open();
                result = command.ExecuteNonQuery();
                command.Parameters.Clear();
                return result;
            }
        }

        /// <summary>
        /// 执行有参数的sql语句
        /// </summary>
        /// <param name="sqlCommand"></param>
        /// <param name="para"></param>
        /// <returns></returns>
        public int ExecuteNonQuery(String sqlCommand, Dictionary<String, object> para)
        {
            int result = 0;
            using (DbConnection connection = GetConnection())
            {
                DbCommand command = GetCommand(sqlCommand, CommandType.Text, connection);
                foreach (KeyValuePair<String, object> p in para)
                {
                    command.Parameters.Add(GetParameter(p.Key, p.Value, command));
                }
                connection.Open();
                result = command.ExecuteNonQuery();
                command.Parameters.Clear();
                return result;
            }
        }

        /// <summary>
        /// 执行有返回值的sql语句
        /// </summary>
        /// <param name="cmdText"></param>
        /// <returns></returns>
        public object ExecuteScalar(String cmdText)
        {
            using (DbConnection connection = GetConnection())
            {
                DbCommand command = GetCommand(cmdText, CommandType.Text, connection);
                connection.Open();
                object val = command.ExecuteScalar();
                command.Parameters.Clear();
                return val;
            }
        }

        /// <summary>
        /// 执行有返回值有参数的sql语句
        /// </summary>
        /// <param name="cmdText"></param>
        /// <param name="para"></param>
        /// <returns></returns>
        public object ExecuteScalar(String cmdText, Dictionary<String, object> para)
        {
            using (DbConnection connection = GetConnection())
            {
                DbCommand command = GetCommand(cmdText, CommandType.Text, connection);
                foreach (KeyValuePair<String, object> p in para)
                {
                    command.Parameters.Add(GetParameter(p.Key, p.Value, command));
                }
                connection.Open();
                object val = command.ExecuteScalar();
                command.Parameters.Clear();
                return val;
            }

        }

        #endregion

        #region

        /// <summary>
        /// 执行无参数的sql语句,返回DbDataReader对象
        /// </summary>
        /// <param name="sqlCommand"></param>
        /// <returns></returns>
        public DbDataReader GetReader(String sqlCommand)
        {
            try
            {
                DbConnection connection = GetConnection();
                DbCommand command = GetCommand(sqlCommand, CommandType.Text, connection);
                connection.Open();
                DbDataReader reader = command.ExecuteReader(CommandBehavior.CloseConnection);
                command.Parameters.Clear();
                return reader;
            }
            catch (Exception ex)
            {
                Console.Write("" + ex.Message);
                return null;
            }
        }

        #endregion

        /// <summary>
        /// 执行有参数的sql语句,返回DbDataReader对象
        /// </summary>
        /// <param name="sqlCommand"></param>
        /// <param name="parameters"></param>
        /// <returns></returns>
        public DbDataReader GetReader(String sqlCommand, Dictionary<String, object> parameters)
        {
            try
            {
                DbConnection connection = GetConnection();
                DbCommand command = GetCommand(sqlCommand, CommandType.Text, connection);
                foreach (KeyValuePair<String, object> p in parameters)
                {
                    command.Parameters.Add(GetParameter(p.Key, p.Value, command));
                }
                connection.Open();
                DbDataReader reader = command.ExecuteReader(CommandBehavior.CloseConnection);
                command.Parameters.Clear();
                return reader;
            }
            catch (Exception ex)
            {
                Console.Write("" + ex.Message);
                return null;
            }
        }

        /// <summary>
        /// 返回DataTable对象
        /// </summary>
        /// <param name="safeSql"></param>
        /// <returns></returns>
        public DataTable GetDataSet(String safeSql)
        {
            /*DbProviderFactory _factory = DbProviderFactories.GetFactory(providerName);
        DbConnection connection = GetConnection();
        connection.Open();
        DbDataAdapter da = _factory.CreateDataAdapter();
        da.SelectCommand = connection.CreateCommand();
        da.SelectCommand.CommandText = safeSql;
        DataTable dt = new DataTable();
        da.Fill(dt);
        return dt;*/

            using (DbConnection connection = GetConnection())
            {
                DbProviderFactory factory = DbProviderFactories.GetFactory(ProviderName);
                DbCommand command = GetCommand(safeSql, CommandType.Text, connection);
                connection.Open();
                DbDataAdapter da = factory.CreateDataAdapter();
                da.SelectCommand = command;
                DataTable datatable = new DataTable();
                da.Fill(datatable);
                return datatable;
            }
        }

        #endregion

        #region Private Method

        /// <summary>
        /// GetConnection 用于获取连接数据库的 connection 对象
        /// </summary>
        /// <returns></returns>
        private DbConnection GetConnection()
        {
            DbConnection connection = DbProviderFactories.GetFactory(ProviderName).CreateConnection();
            connection.ConnectionString = this.connectionString;
            return connection;

        }

        /// <summary>
        /// GetCommand 获取命令参数 command 对象
        /// </summary>
        /// <param name="commandText"></param>
        /// <param name="commandType"></param>
        /// <param name="connection"></param>
        /// <returns></returns>
        private DbCommand GetCommand(String commandText, CommandType commandType, DbConnection connection)
        {
            DbCommand command = connection.CreateCommand();
            command.CommandText = commandText;
            command.CommandType = commandType;
            return command;
        }

        /// <summary>
        /// GetCommand 方法重载
        /// </summary>
        /// <param name="commandText">sql语句</param>
        /// <param name="connection"></param>
        /// <returns></returns>
        private DbCommand GetCommand(String commandText, DbConnection connection)
        {
            DbCommand command = connection.CreateCommand();
            command.CommandText = commandText;
            command.CommandType = CommandType.Text;
            return command;
        }

        /// <summary>
        /// GetParameter 用于为命令设置参数
        /// </summary>
        /// <param name="paramName"></param>
        /// <param name="paramValue"></param>
        /// <param name="command"></param>
        /// <returns></returns>
        private DbParameter GetParameter(String paramName, object paramValue, DbCommand command)
        {
            DbParameter parameter = command.CreateParameter();
            parameter.ParameterName = paramName;
            parameter.Value = paramValue;
            return parameter;
        }

        #endregion
    }
}
